USE [Weval test]
GO
/* work needs to be done on this query and the accompanying HTML 
Filtering options still need to be implemented and testing needs to be
done on the query to ensure its returning correct data.*/
/****** Object:  StoredProcedure [dbo].[ClassesTaught]    Script Date: 7/8/2013 7:20:54 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[SharedReports] 
@instructor int = null,
@department varchar(4) = null,
@year smallint = null,
@semester tinyint = null,
@course varchar(5) = null
AS

--DECLARE @instructorID int = 887969243, @year int = 2013;


WITH cteMain AS 
(
	SELECT
		bannerCRN,
		Semester,
		Year,
		SubjectCode,
		CourseNumber,
		ST.instructorID,
		R.studentID,
		T.ID testID,
		T.testName 
	FROM SectionsTaught ST 
		INNER JOIN Results R ON R.sectionID = ST.sectionID 
			AND ST.instructorID = coalesce(@instructor, ST.instructorID)
			AND (R.deleted = 0 OR R.deleted IS NULL)
		INNER JOIN CourseSections CS ON CS.ID = R.sectionID  
			AND CS.subjectCode = coalesce(@department, CS.subjectCode)
			AND CS.year = coalesce(@year, CS.year)
			AND CS.semester = coalesce(@semester, CS.semester)
			AND CS.courseNumber = coalesce(@course, CS.courseNumber)
		INNER JOIN QuestionResponses QR ON QR.resultID = R.ID
		INNER JOIN Questions Q ON Q.ID = QR.questionID
		INNER JOIN Categories C on C.ID = Q.categoryID
					AND LOWER(C.categoryName) <> 'other' 
		INNER JOIN Tests T ON T.ID = Q.testID 
			AND T.typeID = 5 --Course Eval
		INNER JOIN DistractorResponses DR ON DR.questionResponseID = QR.ID
		INNER JOIN Distractors D ON D.ID = DR.distractorID
		INNER JOIN ShareEvals se ON U.Id = se.instructorId
)
SELECT *
FROM 
	cteMain
GO

sharedreports @year = 2014, @instructor = 880519503